package com.workcell.workdata.xthings.mapper.mqtt;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.workcell.workdata.xthings.entity.mqtt.FlowMeterData;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.Date;

/**
 * @Author 廖博文
 * @Date 2022/11/18 15:24
 * @Description 流量计数据
 */
@Mapper
public interface FlowMeterDataMapper extends BaseMapper<FlowMeterData> {

	/**
	 * 查询设备最近的一次记录
	 * @param deviceNo
	 * @param time
	 * @return
	 */
	@Select("SELECT*,(\n" +
			"SELECT FORMAT((\n" +
			"SELECT cum_flow FROM t_flowmeter_data WHERE device_no=a.device_no AND manufacturer_code=a.manufacturer_code AND `current_time`>=DATE_SUB(NOW(),INTERVAL 1 HOUR) ORDER BY `current_time` DESC LIMIT 1)-(\n" +
			"SELECT cum_flow FROM t_flowmeter_data WHERE device_no=a.device_no AND manufacturer_code=a.manufacturer_code AND `current_time`>=DATE_SUB(NOW(),INTERVAL 1 HOUR) ORDER BY `current_time` ASC LIMIT 1),3)) cumFlowDiffDataHour,(\n" +
			"SELECT FORMAT((\n" +
			"SELECT cum_flow FROM t_flowmeter_data WHERE device_no=a.device_no AND manufacturer_code=a.manufacturer_code AND `current_time`>=DATE_SUB(NOW(),INTERVAL 24 HOUR) ORDER BY `current_time` DESC LIMIT 1)-(\n" +
			"SELECT cum_flow FROM t_flowmeter_data WHERE device_no=a.device_no AND manufacturer_code=a.manufacturer_code AND `current_time`>=DATE_SUB(NOW(),INTERVAL 24 HOUR) ORDER BY `current_time` ASC LIMIT 1),3)) cumFlowDiffDataDay,(\n" +
			"SELECT FORMAT((\n" +
			"SELECT cum_flow FROM t_flowmeter_data WHERE device_no=a.device_no AND manufacturer_code=a.manufacturer_code AND `current_time`>=DATE_SUB(NOW(),INTERVAL 1 MONTH) ORDER BY `current_time` DESC LIMIT 1)-(\n" +
			"SELECT cum_flow FROM t_flowmeter_data WHERE device_no=a.device_no AND manufacturer_code=a.manufacturer_code AND `current_time`>=DATE_SUB(NOW(),INTERVAL 1 MONTH) ORDER BY `current_time` ASC LIMIT 1),3)) cumFlowDiffDataMonth,(\n" +
			"SELECT FORMAT((\n" +
			"SELECT cum_flow FROM t_flowmeter_data WHERE device_no=a.device_no AND manufacturer_code=a.manufacturer_code AND `current_time`>=DATE_ADD(CURDATE(),INTERVAL-DAY (CURDATE())+1 DAY) ORDER BY `current_time` DESC LIMIT 1)-(\n" +
			"SELECT cum_flow FROM t_flowmeter_data WHERE device_no=a.device_no AND manufacturer_code=a.manufacturer_code AND `current_time`>=DATE_ADD(CURDATE(),INTERVAL-DAY (CURDATE())+1 DAY) ORDER BY `current_time` ASC LIMIT 1),3)) cumFlowDiffDataSameMonth FROM t_flowmeter_data a WHERE fm_id=(\n" +
			"SELECT MAX(fm_id) aa FROM t_flowmeter_data WHERE device_no=#{deviceNo} AND manufacturer_code=#{manufacturerCode} AND `current_time`>=DATE_SUB(NOW(),INTERVAL #{time} MINUTE));")
	FlowMeterData getLatelyDataInfoByDeviceNo(@Param("deviceNo") String deviceNo,@Param("manufacturerCode") String manufacturerCode, @Param("time") Integer time);

	/**
	 * 获取硬件时间区间内的最近一笔数据
	 * @param deviceNo
	 * @return
	 */
	@Select("select * from t_flowmeter_data where device_no = #{deviceNo} AND manufacturer_code =#{manufacturerCode} and `current_time` >= #{startTime} and `current_time` < #{endTime} order by `current_time` desc LIMIT 1;")
	FlowMeterData getDataHourLineChartInfo(@Param("deviceNo") String deviceNo,@Param("manufacturerCode") String manufacturerCode, @Param("startTime") Date startTime, @Param("endTime") Date endTime);

	/**
	 * 查询报工数据
	 * @param deviceNo
	 * @param manufacturerCode
	 * @param startTime
	 * @param endTime
	 * @return
	 */
	@Select("SELECT *,( " +
			"SELECT FORMAT(( " +
			"SELECT cum_flow FROM t_flowmeter_data WHERE device_no=a.device_no AND manufacturer_code=a.manufacturer_code AND `current_time`>=#{startTime} AND `current_time`<=#{endTime} " +
			"ORDER BY `current_time` DESC LIMIT 1)-( " +
			"SELECT cum_flow FROM t_flowmeter_data WHERE device_no=a.device_no AND manufacturer_code=a.manufacturer_code AND `current_time`>=#{startTime} " +
			"AND `current_time`<=#{endTime} " +
			"ORDER BY `current_time` ASC LIMIT 1),3)) cumFlowDiffDataHour FROM t_flowmeter_data a WHERE fm_id=( " +
			"SELECT MAX(fm_id) aa FROM t_flowmeter_data WHERE device_no=#{deviceNo} AND manufacturer_code=#{manufacturerCode} AND `current_time` >= #{startTime} " +
			"AND `current_time`<=#{endTime} );")
	FlowMeterData getDataHourInfo(@Param("deviceNo") String deviceNo,@Param("manufacturerCode") String manufacturerCode,
										@Param("startTime") Date startTime, @Param("endTime") Date endTime);

}
